package tw.com.softleader.reins.demo.smartphone.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import tw.com.softleader.reins.demo.smartphone.entity.SmartPhone;

@Repository
public class SmartPhoneDAOImplement implements SmartPhoneDAO {
	@Autowired
	private DataSource dataSource;

	private final String SELECT_ALL = "SELECT * FROM smartphone";

	public List<SmartPhone> selectAll() {
		List<SmartPhone> smartPhones = new ArrayList<SmartPhone>();

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(SELECT_ALL);
			rs = stmt.executeQuery();

			while (rs.next()) {
				SmartPhone smartPhone = new SmartPhone();
				smartPhone.setId(rs.getLong("id"));
				smartPhone.setName(rs.getString("name"));
				smartPhone.setCpu(rs.getString("cpu"));
				smartPhone.setResWidth(rs.getInt("resWidth"));
				smartPhone.setResHeight(rs.getInt("resHeight"));
				smartPhone.setSystem(rs.getString("system"));

				smartPhones.add(smartPhone);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return smartPhones;
	}

	private final String SELECT_BY_ID = "SELECT * FROM smartphone WHERE id = ?";

	@Override
	public SmartPhone selectById(Long id) {
		SmartPhone smartPhone = new SmartPhone();

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_ID);
			stmt.setLong(1, id);
			rs = stmt.executeQuery();

			rs.next();
			smartPhone.setId(rs.getLong("id"));
			smartPhone.setName(rs.getString("name"));
			smartPhone.setCpu(rs.getString("cpu"));
			smartPhone.setResWidth(rs.getInt("resWidth"));
			smartPhone.setResHeight(rs.getInt("resHeight"));
			smartPhone.setSystem(rs.getString("system"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return smartPhone;
	}

	private final String INSERT = "INSERT INTO smartphone(id, name, cpu, resWidth, resHeight, system) VALUES(?, ?, ?, ?, ?, ?)";

	@Override
	public Integer insert(SmartPhone smartPhone) {
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(INSERT);
			stmt.setLong(1, smartPhone.getId());
			stmt.setString(2, smartPhone.getName());
			stmt.setString(3, smartPhone.getCpu());
			stmt.setInt(4, smartPhone.getResWidth());
			stmt.setInt(5, smartPhone.getResHeight());
			stmt.setString(6, smartPhone.getSystem());

			return stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private final String UPDATE = "UPDATE smartphone SET name = ?, cpu = ?, resWidth = ?, resHeight = ?, system = ? WHERE id = ?";

	@Override
	public Integer update(SmartPhone smartPhone) {
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(UPDATE);
			stmt.setString(1, smartPhone.getName());
			stmt.setString(2, smartPhone.getCpu());
			stmt.setInt(3, smartPhone.getResWidth());
			stmt.setInt(4, smartPhone.getResHeight());
			stmt.setString(5, smartPhone.getSystem());
			stmt.setLong(6, smartPhone.getId());

			return stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private final String DELETE = "DELETE FROM smartphone WHERE id = ?";

	@Override
	public Integer delete(Long id) {
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(DELETE);
			stmt.setLong(1, id);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
